﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.AnalysisServices;
using System.Xml;
using System.Text;
using System.Globalization;
using System.IO;

namespace InformacionCubos
{
    public partial class _Default : System.Web.UI.Page
    {
        public static string fnCadenaConexion(string servidor)
        {
            //return "Provider=MSOLAP;Data Source="+servidor+";User Id=BANCOAZTECA\\B702674;Password=Heybrajham181106/;";
            return "Provider=MSOLAP;Data Source=" + servidor + ";";
            //return "Provider=MSOLAP;Persist Security Info=True;Data Source=10.63.85.191;Mode=Read;";
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                try
                {
                    ListBD.DataSource = LeerBDs(ListServidores.SelectedValue);
                    ListBD.DataTextField = "Nombre";
                    ListBD.DataValueField = "ID";
                    ListBD.DataBind();

                    ListCubos.DataSource = LeerCubos(ListServidores.SelectedValue, ListBD.SelectedValue);
                    ListCubos.DataTextField = "Nombre";
                    ListCubos.DataValueField = "ID";
                    ListCubos.DataBind();
                }
                catch (Exception ex)
                {
                    
                }
            }
        }

        public DataTable LeerBDs(string ServerName)
        {
            var server = new Server();
            string cadenaConexion = fnCadenaConexion(ListServidores.SelectedValue);
            try
            {
                server.Connect(cadenaConexion);
                DataTable BasesDatos = new DataTable();
                BasesDatos.Columns.Add("ID");
                BasesDatos.Columns.Add("Nombre");
                foreach (Database database in server.Databases)
                {
                    BasesDatos.Rows.Add(database.ID, database.Name);
                }
                server.Disconnect();
                BasesDatos.DefaultView.Sort = "Nombre ASC";
                return BasesDatos;
            }
            catch (Exception ex)
            {
                Response.Write("<SCRIPT>alert('" + ex.ToString() + "');</SCRIPT>");
                throw(ex);
            }
        }

        public DataTable LeerCubos(string ServerName,string BDName)
        {
            var server = new Server();
            string cadenaConexion = fnCadenaConexion(ListServidores.SelectedValue);
            server.Connect(cadenaConexion);
            DataTable cubos = new DataTable();
            cubos.Columns.Add("ID");
            cubos.Columns.Add("Nombre");
            cubos.Rows.Add("ALL", "* Todos *");

            Database database=server.Databases[BDName];
            foreach (Cube cubo in database.Cubes)
            {
                cubos.Rows.Add(cubo.ID, cubo.Name);
            }
            server.Disconnect();
            cubos.DefaultView.Sort = "Nombre ASC";

            return cubos;
        }

        protected DataTable LogSSASInfo(string ServerName,string opc)
        {
            var server = new Server();
            
            //server.Connect(serverName);
            string cadenaConexion = fnCadenaConexion(ListServidores.SelectedValue);
            server.Connect(cadenaConexion);
            DataTable tabla = new DataTable();
            CultureInfo ci = new CultureInfo("es-MX");
            if (opc == "Particiones")
            {
                //tabla.Columns.Add("Database");
                tabla.Columns.Add("Cubo");
                tabla.Columns.Add("Meassure");
                tabla.Columns.Add("Particion");
                tabla.Columns.Add("Tamaño (MB)", typeof(double));
                tabla.Columns.Add("Status");
                tabla.Columns.Add("Fecha", typeof(DateTime));
                tabla.Columns.Add("IDCubo");
                tabla.Columns.Add("IDMeassure");
                tabla.Columns.Add("IDParticion");

				if (ListCubos.SelectedValue=="ALL")
				{
                foreach (Cube cube in server.Databases[ListBD.Text].Cubes)
                {
                    //Console.WriteLine("     Cube: " + cube.Name + " " + cube.LastProcessed + " " + cube.LastSchemaUpdate);

                    foreach (MeasureGroup measureGroup in cube.MeasureGroups)
                    {
                        //Console.WriteLine("         Measure Group: " + measureGroup.Name + " " + measureGroup.LastProcessed);

                        foreach (Partition partition in measureGroup.Partitions)
                        {
                            //Console.WriteLine("             Partition: " + partition.Name + " " + partition.LastProcessed); 
                            //tabla.Rows.Add(server.Databases[ListBD.Text].Name, cube.Name, measureGroup.Name, partition.Name, partition.EstimatedSize,partition.State, string.Format("{0:yyyy-MM-dd HH:mm}", partition.LastProcessed));
                            //tabla.Rows.Add(cube.Name, measureGroup.Name, partition.Name, Math.Round(partition.EstimatedSize/1048576.00,2)/*de bytes a MB dividiendo sobre (1024*1024)*/, partition.State, string.Format("{0:yyyy-MM-dd HH:mm}", partition.LastProcessed),cube.ID,measureGroup.ID,partition.ID);
                            tabla.Rows.Add(cube.Name, measureGroup.Name, partition.Name, Math.Round(partition.EstimatedSize / 1048576.00, 2)/*de bytes a MB dividiendo sobre (1024*1024)*/, partition.State, partition.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss",ci), cube.ID, measureGroup.ID, partition.ID);
                        }
                    }
                }
				}
				else
				{
                    foreach (MeasureGroup measureGroup in server.Databases[ListBD.Text].Cubes[ListCubos.Text].MeasureGroups)
                    {
                        //Console.WriteLine("         Measure Group: " + measureGroup.Name + " " + measureGroup.LastProcessed);

                        foreach (Partition partition in measureGroup.Partitions)
                        {
                            //Console.WriteLine("             Partition: " + partition.Name + " " + partition.LastProcessed);
                            //tabla.Rows.Add(server.Databases[ListBD.Text].Name, cube.Name, measureGroup.Name, partition.Name, partition.EstimatedSize,partition.State, string.Format("{0:yyyy-MM-dd HH:mm}", partition.LastProcessed));
                            tabla.Rows.Add(measureGroup.Parent.Name, measureGroup.Name, partition.Name, Math.Round(partition.EstimatedSize / 1048576.00, 2)/*de bytes a MB dividiendo sobre (1024*1024)*/, partition.State, partition.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss",ci), measureGroup.Parent.ID, measureGroup.ID, partition.ID);
                        }
                    }
                }
            }
            if (opc == "Dimensiones") //Mostrar Dimensiones?
            {
                tabla.Columns.Add("Database");
                tabla.Columns.Add("Dimension");
                tabla.Columns.Add("Status");
                tabla.Columns.Add("Fecha", typeof(DateTime));
                tabla.Columns.Add("IDDimension");
                if (ListCubos.SelectedValue == "ALL")
                {
                    foreach (Dimension dimension in server.Databases[ListBD.Text].Dimensions)
                    {
                        //Console.WriteLine(" Dimension: " + dimension.Name + " " + dimension.LastProcessed);
                        tabla.Rows.Add(server.Databases[ListBD.Text].Name, dimension.Name, dimension.State, dimension.LastProcessed, dimension.ID);
                    }
                }
                else
                {
                    foreach (CubeDimension cuboDimension in server.Databases[ListBD.Text].Cubes[ListCubos.SelectedValue].Dimensions)
                    {
                        tabla.Rows.Add(server.Databases[ListBD.Text].Name, cuboDimension.Dimension.Name, cuboDimension.Dimension.State, cuboDimension.Dimension.LastProcessed, cuboDimension.Dimension.ID);
                    }
                }
            }
            

            //foreach (Database database in server.Databases)
            //{
            //    Console.WriteLine(database.Name + " " + database.LastUpdate + " " + database.EstimatedSize / 1024 + " " + database.CreatedTimestamp);
            //    if (opc==1){ //Mostrar Particiones
            //    foreach (Cube cube in database.Cubes)
            //    {
            //        Console.WriteLine("     Cube: " + cube.Name + " " + cube.LastProcessed + " " + cube.LastSchemaUpdate);

            //        foreach (MeasureGroup measureGroup in cube.MeasureGroups)
            //        {
            //            Console.WriteLine("         Measure Group: " + measureGroup.Name + " " + measureGroup.LastProcessed);

            //            foreach (Partition partition in measureGroup.Partitions)
            //            {
            //                Console.WriteLine("             Partition: " + partition.Name + " " + partition.LastProcessed);
            //                tabla.Rows.Add(database.Name,cube.Name, measureGroup.Name, partition.Name, partition.State,string.Format( "{0:yyyy-MM-dd HH:mm}",partition.LastProcessed));
            //            }
            //        }
            //    }
            //    }
            //    if (opc == 2) //Mostrar Dimensiones
            //    {
            //        foreach (Dimension dimension in database.Dimensions)
            //        {
            //            Console.WriteLine(" Dimension: " + dimension.Name + " " + dimension.LastProcessed);
            //            tabla.Rows.Add(database.Name, dimension.Name, dimension.State, dimension.LastProcessed);
            //        }
            //    }
            //    Console.WriteLine("");
            //    Console.WriteLine("------------------------------------------------");
            //    Console.WriteLine("");
            //}
            if (opc == "Particiones") { tabla.DefaultView.Sort = "Cubo,Meassure,Status,Fecha DESC"; } //ordenar particiones
            if (opc == "Dimensiones") { tabla.DefaultView.Sort = "Database,Status,Fecha DESC"; } //ordenar Dimensiones
            
            server.Disconnect();
            cargaListaOrdenamiento(tabla);
            return tabla;
        }

        protected void cargaListaOrdenamiento(DataTable tabla)
        {
            foreach (DataColumn columna in tabla.Columns)
            {
                listOrdenamiento.Items.Add(columna.ColumnName);
            }
        }

        protected void btnCargaParticiones_Click(object sender, EventArgs e)
        {
            ObjetosCargados.Value = "Particiones";
            RBLTipoProcess.Items[1].Enabled = false; //Process UPDATE deshabilitado
            RBLTipoProcess.SelectedIndex= 0; //Process FULL seleccionado
            GridView1.DataSource = null;
            GridView1.DataSource = LogSSASInfo(ListServidores.SelectedValue,ObjetosCargados.Value);
            GridView1.DataBind();
        }

        protected void btnCargaDimensiones_Click(object sender, EventArgs e)
        {
            //Response.Write("<SCRIPT>alert('" + ListServidores.SelectedValue + "');</SCRIPT>");
            ObjetosCargados.Value = "Dimensiones";
            RBLTipoProcess.Items[1].Enabled = true; //Process UPDATE deshabilitado
            GridView1.DataSource = null;
            GridView1.DataSource = LogSSASInfo(ListServidores.SelectedValue, ObjetosCargados.Value);
            GridView1.DataBind();
        }

        protected void GridView1_PreRender(object sender, EventArgs e)
        {
            //GridDecorator.MergeRowsColumn(GridView1, 0);
            //GridDecorator.MergeRowsColumn(GridView1, 1);
            //GridDecorator.MergeRowsColumn(GridView1, 2);
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            var server = new Server();
            server.Connect(fnCadenaConexion(ListServidores.SelectedValue));
            TreeNode nodoRaiz=new TreeNode();
            nodoRaiz.Text=ListServidores.SelectedValue;
            ArbolCubos.Nodes.Add(nodoRaiz);
            foreach (Database baseDatos in server.Databases)
            {
                TreeNode nodoBD=new TreeNode();
                nodoBD.Text=baseDatos.Name;
                nodoRaiz.ChildNodes.Add(nodoBD);
                TreeNode nodoCubosRoot = new TreeNode("Cubos");
                TreeNode nodoDimRoot = new TreeNode("Dimensiones");
                nodoBD.ChildNodes.Add(nodoCubosRoot);
                nodoBD.ChildNodes.Add(nodoDimRoot);
                foreach (Cube cubos in baseDatos.Cubes)
                {
                    TreeNode nodoCubo = new TreeNode(cubos.Name);
                    nodoCubosRoot.ChildNodes.Add(nodoCubo);
                    foreach (MeasureGroup meassure in cubos.MeasureGroups)
                    {
                        TreeNode nodoMeassure = new TreeNode(meassure.Name);
                        nodoCubo.ChildNodes.Add(nodoMeassure);
                        foreach (Partition particion in meassure.Partitions)
                        {
                            TreeNode nodoParticion = new TreeNode(particion.Name);
                            nodoMeassure.ChildNodes.Add(nodoParticion);
                        }
                    }
                }
                foreach (Dimension dimension in baseDatos.Dimensions)
                {
                    TreeNode nodoDimension = new TreeNode(dimension.Name);
                    nodoDimRoot.ChildNodes.Add(nodoDimension);
                }

            }
        }

        protected void ArbolCubos_SelectedNodeChanged(object sender, EventArgs e)
        {
            string cadena=ArbolCubos.SelectedNode.ValuePath;
            //Response.Write("<SCRIPT>alert('" + cadena + "');</SCRIPT>");
        }

        protected void ListServidores_SelectedIndexChanged(object sender, EventArgs e)
        {
            ListBD.DataSource = LeerBDs(ListServidores.SelectedValue);
            ListBD.DataTextField = "Nombre";
            ListBD.DataValueField = "ID";
            ListBD.DataBind();

            ListCubos.DataSource = LeerCubos(ListServidores.SelectedValue, ListBD.SelectedValue);
            ListCubos.DataTextField = "Nombre";
            ListCubos.DataValueField = "ID";
            ListCubos.DataBind();

            GridView1.DataSource = null;
            GridView1.DataBind();
        }

        protected void ListBD_SelectedIndexChanged(object sender, EventArgs e)
        {
            ListCubos.DataSource = LeerCubos(ListServidores.SelectedValue, ListBD.SelectedValue);
            ListCubos.DataTextField = "Nombre";
            ListCubos.DataValueField = "ID";
            ListCubos.DataBind();
            GridView1.DataSource = null;
            GridView1.DataBind();
        }

        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        {
            string direccion=ViewState["DireccionSort"] as string;

            if ((direccion != null) && (direccion == "DESC"))
            {
                direccion="ASC";
            }
            else { direccion = "DESC"; }

            DataTable dt = new DataTable();
            dt = LogSSASInfo(ListServidores.SelectedValue, ObjetosCargados.Value);
            dt.DefaultView.Sort = e.SortExpression + " "+direccion;

            GridView1.DataSource = dt;
            GridView1.DataBind();
            //Response.Write("<SCRIPT>alert('" + e.SortExpression + " "+direccion+"');</SCRIPT>");

            ViewState["DireccionSort"] = direccion;
        }

        protected void listUpBtn_Click(object sender, EventArgs e)
        {
            int IndiceSeleccionado = listOrdenamiento.SelectedIndex;
            if (IndiceSeleccionado == -1) // nothing selected 
                return;
            if (IndiceSeleccionado == 0) // already at top of list   
                return;

            ListItem Temp = listOrdenamiento.SelectedItem;
            listOrdenamiento.Items.Remove(listOrdenamiento.SelectedItem);
            listOrdenamiento.Items.Insert(IndiceSeleccionado - 1, Temp);
        }

        protected void listDownBtn_Click(object sender, EventArgs e)
        {
            int IndiceSeleccionado = listOrdenamiento.SelectedIndex;
            if (IndiceSeleccionado == -1) // nothing selected 
                return;
            if (IndiceSeleccionado == listOrdenamiento.Items.Count-1) // already at top of list   
                return;

            ListItem Temp = listOrdenamiento.SelectedItem;
            listOrdenamiento.Items.Remove(listOrdenamiento.SelectedItem);
            listOrdenamiento.Items.Insert(IndiceSeleccionado + 1, Temp);
        }//listDownBtn_Click

        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            switch (e.CommandName)
            {
                case "Procesar":
                    int idxFila = Convert.ToInt32(e.CommandArgument); //Indice de la fila almacenado en CommandArgument
                    GridViewRow filaSeleccionada = ((GridView)e.CommandSource).Rows[idxFila]; //Almacena la Fila
                    var server = new Server();
                    string cadenaConexion = fnCadenaConexion(ListServidores.SelectedValue);
                    server.Connect(cadenaConexion);
                    //Procesa a FULL
                    if (ObjetosCargados.Value == "Particiones")
                    {
                        server.Databases[ListBD.SelectedValue].Cubes[filaSeleccionada.Cells[7].Text].MeasureGroups[filaSeleccionada.Cells[8].Text].Partitions[filaSeleccionada.Cells[9].Text].Process(ProcessType.ProcessFull);
                        Response.Write("<SCRIPT>alert('" + RBLTipoProcess.SelectedItem.Text + " Terminado exitosamente sobre la particion "+ filaSeleccionada.Cells[3].Text +"!!!');</SCRIPT>");
                    }
                    else if (ObjetosCargados.Value == "Dimensiones")
                    {
                        if (RBLTipoProcess.SelectedValue=="ProcessFULL")
                        {
                            server.Databases[ListBD.SelectedValue].Dimensions[filaSeleccionada.Cells[5].Text].Process(ProcessType.ProcessFull);
                        }
                        else if (RBLTipoProcess.SelectedValue == "ProcessUPDATE")
                        {
                            server.Databases[ListBD.SelectedValue].Dimensions[filaSeleccionada.Cells[5].Text].Process(ProcessType.ProcessUpdate);
                        }
                        Response.Write("<SCRIPT>alert('" + RBLTipoProcess.SelectedItem.Text + " Terminado exitosamente sobre la Dimension " + filaSeleccionada.Cells[2].Text + "!!!');</SCRIPT>");
                    }
                    //Mensaje de que termino de procesar
                    

                    //Actualizar Datos
                    GridView1.DataSource = LogSSASInfo(ListServidores.SelectedValue, ObjetosCargados.Value);
                    GridView1.DataBind();
                    
                    server.Disconnect();
                    //Response.Write("<SCRIPT>alert('" + Convert.ToInt32(e.CommandArgument).ToString() +"');</SCRIPT>");
                    break;
                default:
                    break;
            }

            
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (ObjetosCargados.Value=="Particiones")
            {
                e.Row.Cells[7].Visible = false; //IDCubos
                e.Row.Cells[8].Visible = false; //IDMeassure
                e.Row.Cells[9].Visible = false; //IDParticion
            }
            else if (ObjetosCargados.Value=="Dimensiones")
            {
                e.Row.Cells[5].Visible=false; //IDDimension
            }
        }

        protected void btnGetXMLA_Click(object sender, EventArgs e)
        {
            if (ListBD.SelectedIndex!=-1)
            {
                string filePath = @"~/"+ ListBD.SelectedValue +".xmla";
                var server = new Server();
                string cadenaConexion = fnCadenaConexion(ListServidores.SelectedValue);
                server.Connect(cadenaConexion);
                //Connect to the server.
                Database db = server.Databases.FindByName(ListBD.SelectedValue);
                Scripter scripter = new Scripter();
                string fileName = Path.GetFileName(filePath);
                //Create an XML text writer, which will be used to script the command.
                System.Xml.XmlTextWriter writer = new
                System.Xml.XmlTextWriter(Server.MapPath(fileName),Encoding.UTF8);
                //Create an array of major objects. In this example, the array contains
                //just one element.
                MajorObject[] objects = new MajorObject [] { db };
                //Script the Alter command.
                scripter.ScriptCreate(objects, writer, false);
                //Close the XML writer.
                //Response.Write("<SCRIPT>alert('" + writer.ToString() + "');</SCRIPT>");
                writer.Close();

                Response.ContentType = "application/octet-stream";
                Response.AppendHeader("content-disposition", "attachment; filename="+fileName);
                Response.TransmitFile(Server.MapPath(filePath));
                Response.End();

                server.Disconnect();


            }
        }

    }

    public class GridDecorator
    {
        public static void MergeRows(GridView gridView)
        {
            for (int rowIndex = gridView.Rows.Count - 2; rowIndex >= 0; rowIndex--)
            {
                GridViewRow row = gridView.Rows[rowIndex];
                GridViewRow previousRow = gridView.Rows[rowIndex + 1];

                for (int i = 0; i < row.Cells.Count; i++)
                {
                    if (i!=4){
                    if (row.Cells[i].Text == previousRow.Cells[i].Text)
                    {
                        row.Cells[i].RowSpan = previousRow.Cells[i].RowSpan < 2 ? 2 :
                                               previousRow.Cells[i].RowSpan + 1;
                        previousRow.Cells[i].Visible = false;
                    }}
                }
            }
        }

        public static void MergeRowsColumn(GridView gridView,int Column)
        {
           //index of a column where cells should be merged
           int cellIndex = Column;
           int prevColor = 1;
           for (int rowIndex = gridView.Rows.Count - 2; rowIndex >= 0; rowIndex--)
           {
                GridViewRow row = gridView.Rows[rowIndex];
                GridViewRow previousRow = gridView.Rows[rowIndex + 1];

                if (row.Cells[cellIndex].Text == previousRow.Cells[cellIndex].Text)
                {
                    row.Cells[cellIndex].RowSpan = previousRow.Cells[cellIndex].RowSpan < 2 ? 2 :
                                           previousRow.Cells[cellIndex].RowSpan + 1;
                    previousRow.Cells[cellIndex].Visible = false;
                }
                else
                {
                    if (prevColor == 1)
                    {
                        gridView.Rows[rowIndex].BackColor=System.Drawing.Color.DarkGreen;
                        gridView.Rows[rowIndex].ForeColor=System.Drawing.Color.LightYellow;
                        prevColor = 2;
                    }
                    else
                    {
                        gridView.Rows[rowIndex].BackColor = System.Drawing.Color.MintCream;
                        prevColor = 1;
                    }
                }
            }
        }
    }
}
